﻿using Lufei_CaterModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lufei_CaterDAL
{
    public class RoomDAL
    {

        /// <summary>
        /// 查询所有没有被删除的房间
        /// </summary>
        /// <returns></returns>
        public List<RoomInfo> GetRoomsByDelFlag()
        {
            string sql = "select * from roomInfo where delflag=0";
            DataTable dt= SqlHelper.ExecuteDataTable(sql);

            List<RoomInfo> list = new List<RoomInfo>();
            if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(RowsToRoomInfo(dr));
                }
            }
            return list;
        }

        /// <summary>
        /// 关系转模型
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private RoomInfo RowsToRoomInfo(DataRow dr)
        {
            RoomInfo r = new RoomInfo();
            //r.DelFlag = dr[""].ToString();
            r.IsDefault = Convert.ToInt32(dr["IsDefault"]);
            r.RoomId = Convert.ToInt32(dr["RoomId"]);
            r.RoomMaxConsumer = Convert.ToDecimal(dr["RoomMaxConsumer"]);
            r.RoomMinimunConsume = Convert.ToDecimal(dr["RoomMinimunConsume"]);
            r.RoomName = dr["RoomName"].ToString();
            r.RoomType = Convert.ToInt32(dr["RoomType"]);
            r.SubBy = Convert.ToInt32(dr["SubBy"]);
            r.SubTime = Convert.ToDateTime(dr["SubTime"]);
            return r;

        }

        /// <summary>
        /// 添加房间
        /// </summary>
        /// <returns></returns>
        public int InserRoom(RoomInfo ri)
        {
            string sql = "insert into RoomInfo (roomname,roomtype,RoomMinimunConsume,RoomMaxConsumer,IsDefault,DelFlag,SubTime,SubBy)values(@roomname,@roomtype,@RoomMinimunConsume,@RoomMaxConsumer,@IsDefault,@DelFlag,@SubTime,@SubBy)";
            SQLiteParameter[] param= new SQLiteParameter[] {
                new SQLiteParameter("@roomname",ri.RoomName),
                new SQLiteParameter("@roomtype",ri.RoomType),
                new SQLiteParameter("@RoomMinimunConsume",ri.RoomMinimunConsume),
                new SQLiteParameter("@RoomMaxConsumer",ri.RoomMaxConsumer),
                new SQLiteParameter("@IsDefault",ri.IsDefault),
                new SQLiteParameter("@DelFlag",ri.DelFlag),
                new SQLiteParameter("@SubTime",ri.SubTime),
                new SQLiteParameter("@SubBy",ri.SubBy)
            };

          return  SqlHelper.ExecuteNonQuery(sql,param);

        }


        /// <summary>
        /// 删除房间
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteRoom(int id)
        {
            string sql = "update roominfo set DelFlag=1 where RoomId=" + id;
            return  SqlHelper.ExecuteNonQuery(sql);
        }


        /// <summary>
        /// 根据id获取房间
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public RoomInfo GetRoomByID(int id)
        {
            RoomInfo rm = new RoomInfo();
            string sql = "select * from RoomInfo where RoomId="+id;
             DataTable dt=  SqlHelper.ExecuteDataTable(sql);
            if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    rm = RowsToRoomInfo(dr);
                }
            }
            return rm;
        }


        /// <summary>
        /// 修改房间
        /// </summary>
        /// <returns></returns>
        public int UpdateRoomById(RoomInfo ri)
        {
            string sql = "update roominfo set RoomName=@RoomName,RoomType=@RoomType,RoomMinimunConsume=@RoomMinimunConsume,RoomMaxConsumer=@RoomMaxConsumer,IsDefault=@IsDefault where RoomId=@RoomId;";
            SQLiteParameter[] param = new SQLiteParameter[] {
            new SQLiteParameter("@RoomName",ri.RoomName),
            new SQLiteParameter("@RoomType",ri.RoomType),
            new SQLiteParameter("@RoomMinimunConsume",ri.RoomMinimunConsume),
            new SQLiteParameter("@RoomMaxConsumer",ri.RoomMaxConsumer),
            new SQLiteParameter("@IsDefault",ri.IsDefault),
            new SQLiteParameter("@RoomId",ri.RoomId),
            };

           return SqlHelper.ExecuteNonQuery(sql,param);
        }

    }
}
